/*
 * DBeaver - Universal Database Manager
 * Copyright (C) 2010-2023 DBeaver Corp and others
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jkiss.dbeaver.model.sql;

import org.eclipse.core.resources.IFile;
import org.eclipse.core.runtime.Platform;
import org.jkiss.code.NotNull;
import org.jkiss.code.Nullable;
import org.jkiss.dbeaver.DBException;
import org.jkiss.dbeaver.Log;
import org.jkiss.dbeaver.model.DBPDataKind;
import org.jkiss.dbeaver.model.DBPDataSource;
import org.jkiss.dbeaver.model.DBPObject;
import org.jkiss.dbeaver.model.DBUtils;
import org.jkiss.dbeaver.model.data.*;
import org.jkiss.dbeaver.model.edit.DBEPersistAction;
import org.jkiss.dbeaver.model.exec.DBCSession;
import org.jkiss.dbeaver.model.impl.sql.BasicSQLDialect;
import org.jkiss.dbeaver.model.impl.sql.RelationalSQLDialect;
import org.jkiss.dbeaver.model.runtime.DBRFinder;
import org.jkiss.dbeaver.model.runtime.DBRProgressMonitor;
import org.jkiss.dbeaver.model.runtime.VoidProgressMonitor;
import org.jkiss.dbeaver.model.struct.*;
import org.jkiss.dbeaver.model.struct.rdb.DBSTableForeignKey;
import org.jkiss.dbeaver.model.struct.rdb.DBSTableForeignKeyColumn;
import org.jkiss.dbeaver.utils.ContentUtils;
import org.jkiss.dbeaver.utils.GeneralUtils;
import org.jkiss.utils.ArrayUtils;
import org.jkiss.utils.CommonUtils;
import org.jkiss.utils.Pair;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL Utils
 */
public final class SQLUtils {

    private static final Log log = Log.getLog(SQLUtils.class);

    public static final Pattern PATTERN_OUT_PARAM = Pattern.compile("((\\?)|(:[a-z0-9]+))\\s*:=");
    public static final Pattern PATTERN_SIMPLE_NAME = Pattern.compile("[a-z][a-z0-9_]*", Pattern.CASE_INSENSITIVE);

    private static final Pattern CREATE_PREFIX_PATTERN = Pattern.compile("(CREATE (:OR REPLACE)?).+", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);

    private static final int MIN_SQL_DESCRIPTION_LENGTH = 512;
    private static final int MAX_SQL_DESCRIPTION_LENGTH = 500;

    private static final String DBEAVER_DDL_COMMENT = "-- DDL generated by ";
    private static final String DBEAVER_DDL_WARNING = "-- WARNING: It may differ from actual native database DDL";
    private static final String DBEAVER_SCRIPT_DELIMITER = "$$";

    public static String stripTransformations(String query)
    {
        return query;
//        if (!query.contains(TOKEN_TRANSFORM_START)) {
//            return query;
//        } else {
//            return PATTERN_XFORM.matcher(query).replaceAll("");
//        }
    }

    public static String stripComments(@NotNull SQLDialect dialect, @NotNull String query)
    {
        Pair<String, String> multiLineComments = dialect.getMultiLineComments();
        return stripComments(
            query,
            multiLineComments == null ? null : multiLineComments.getFirst(),
            multiLineComments == null ? null : multiLineComments.getSecond(),
            dialect.getSingleLineComments());
    }

    public static boolean isCommentLine(SQLDialect dialect, String line) {
        for (String slc : dialect.getSingleLineComments()) {
            if (line.startsWith(slc)) {
                return true;
            }
        }
        return false;
    }

    public static String stripComments(@NotNull String query, @Nullable String mlCommentStart, @Nullable String mlCommentEnd, String[] slComments)
    {
        String leading = "", trailing = "";
        {
            int startPos, endPos;
            for (startPos = 0; startPos < query.length(); startPos++) {
                if (!Character.isWhitespace(query.charAt(startPos))) {
                    break;
                }
            }
            for (endPos = query.length() - 1; endPos > startPos; endPos--) {
                if (!Character.isWhitespace(query.charAt(endPos))) {
                    break;
                }
            }
            if (startPos > 0) {
                leading = query.substring(0, startPos);
            }
            if (endPos < query.length() - 1) {
                trailing = query.substring(endPos + 1);
            }
        }
        query = query.trim();
        if (mlCommentStart != null && mlCommentEnd != null && query.startsWith(mlCommentStart)) {
            int endPos = query.indexOf(mlCommentEnd);
            if (endPos != -1) {
                query = query.substring(endPos + mlCommentEnd.length());
            }
        }
        for (int i = 0; i < slComments.length; i++) {
            while (query.startsWith(slComments[i])) {
                int crPos = query.indexOf('\n');
                if (crPos == -1) {
                    // Query is comment line - return empty
                    query = "";
                    break;
                } else {
                    query = query.substring(crPos).trim();
                }
            }
        }
        return leading + query + trailing;
    }

    public static List<String> splitFilter(String filter)
    {
        if (CommonUtils.isEmpty(filter)) {
            return Collections.emptyList();
        }
        return CommonUtils.splitString(filter, ',');
    }

    public static boolean matchesAnyLike(String string, Collection<String> likes)
    {
        for (String like : likes) {
            if (matchesLike(string, like)) {
                return true;
            }
        }
        return false;
    }

    public static boolean isLikePattern(String like)
    {
        return like.indexOf('%') != -1 || like.indexOf('*') != -1 || like.indexOf('_') != -1 || like.indexOf('?') != -1;// || like.indexOf('_') != -1;
    }

    @NotNull
    public static String makeLikePattern(@NotNull String like) {
        StringBuilder result = new StringBuilder();

        for (int i = 0; i < like.length(); i++) {
            char c = like.charAt(i);
            if (c == '*') result.append(".*");
            else if (c == '?' || c == '_') result.append(".");
            else if (c == '%') result.append(".*");
            else if (Character.isLetterOrDigit(c)) result.append(c);
            else if (c == '(' || c == ')' || c == '[' || c == ']') result.append('\\').append(c);
            else if (c == '\\') {
                if (i < like.length() - 1) {
                    char nc = like.charAt(i + 1);
                    if (nc == '_' || nc == '*' || nc == '?' || nc == '.' || nc == '%') {
                        result.append("\\").append(nc);
                        i++;
                    } else {
                        result.append("\\");
                    }
                }
            }
            else {
                result.append(c);
            }
        }
        return result.toString();
    }

    @NotNull
    public static String makeRegexFromLike(@NotNull String clause) {
        final StringBuilder sb = new StringBuilder();
        for (int index = 0, length = clause.length(); index < length; index++) {
            final char ch = clause.charAt(index);
            if (ch == '%') {
                if (index > 0 && index < length - 1) {
                    sb.append(".*");
                }
            } else {
                if (index == 0) {
                    sb.append('^');
                }
                sb.append(ch == '_' ? '.' : ch);
                if (index == length - 1) {
                    sb.append('$');
                }
            }
        }
        return sb.toString();
    }

    public static String makeSQLLike(String like)
    {
        return like.replace("*", "%").replace("?", "_");
    }

    public static boolean matchesLike(String string, String like)
    {
        Pattern pattern = Pattern.compile(makeLikePattern(like), Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
        return pattern.matcher(string).matches();
    }

    public static void appendValue(StringBuilder buffer, DBSTypedObject type, Object value)
    {
        if (type.getDataKind() == DBPDataKind.NUMERIC || type.getDataKind() == DBPDataKind.BOOLEAN) {
            buffer.append(value);
        } else {
            buffer.append('\'').append(value).append('\'');
        }
    }

    public static boolean isStringQuoted(DBSObject object, String string) {
        return object.getDataSource().getSQLDialect().isQuotedString(string);
    }

    public static String quoteString(DBSObject object, String string)
    {
        return quoteString(object.getDataSource(), string);
    }

    public static String quoteString(DBPDataSource dataSource, String string)
    {
        return dataSource.getSQLDialect().getQuotedString(string);
    }

    public static String escapeString(DBPDataSource dataSource, String string)
    {
        return dataSource.getSQLDialect().escapeString(string);
    }

    public static String unQuoteString(DBPDataSource dataSource, String string)
    {
        if (string.length() > 1 && string.charAt(0) == '\'' && string.charAt(string.length() - 1) == '\'') {
            return dataSource.getSQLDialect().unEscapeString(string.substring(1, string.length() - 1));
        }
        return string;
    }

    public static String getFirstKeyword(SQLDialect dialect, String query)
    {
        query = stripComments(dialect, query);
        int startPos = 0, endPos = -1;
        for (int i = 0; i < query.length(); i++) {
            if (Character.isLetterOrDigit(query.charAt(i))) {
                startPos = i;
                break;
            }
        }
        for (int i = startPos; i < query.length(); i++) {
            if (Character.isWhitespace(query.charAt(i))) {
                endPos = i;
                break;
            }
        }
        if (endPos == -1) {
            return query;
        }
        return query.substring(startPos, endPos);
    }

    @Nullable
    public static String getQueryOutputParameter(DBCSession session, String query)
    {
        final Matcher matcher = PATTERN_OUT_PARAM.matcher(query);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return null;
    }

    /**
     * Removes \\r characters from query.
     * Actually this is done specially for Oracle due to some bug in it's driver
     *
     * @param dataSource
     * @param query query
     * @return normalized query
     */
    public static String makeUnifiedLineFeeds(DBPDataSource dataSource, String query)
    {
        SQLDialect dialect = SQLUtils.getDialectFromDataSource(dataSource);
        if (!dialect.isCRLFBroken()) {
            return query;
        }
        if (query.indexOf('\r') == -1) {
            return query;
        }
        StringBuilder result = new StringBuilder(query.length());
        for (int i = 0; i < query.length(); i++) {
            char c = query.charAt(i);
            if (c == '\r') {
                continue;
            }
            result.append(c);
        }
        return result.toString();
    }

    public static void appendLikeCondition(@NotNull StringBuilder sql,@NotNull String value, boolean not,@Nullable SQLDialect dialect) {
        value = makeSQLLike(value);
        if (value.contains("%") || value.contains("_")) {
            if (not) sql.append(" NOT");
            sql.append(" LIKE ?");
            if (dialect instanceof RelationalSQLDialect &&
                ((RelationalSQLDialect) dialect).getLikeEscapeClause(SQLConstants.DEFAULT_LIKE_ESCAPE) != null)
            {
                sql.append(((RelationalSQLDialect) dialect).getLikeEscapeClause(SQLConstants.DEFAULT_LIKE_ESCAPE));
            }
        } else {
            sql.append(not ? "<>?" : "=?");
        }
    }

    public static boolean appendFirstClause(StringBuilder sql, boolean firstClause)
    {
        if (firstClause) {
            sql.append(" WHERE ");
        } else {
            sql.append(" AND ");
        }
        return false;
    }

    public static String trimQueryStatement(SQLSyntaxManager syntaxManager, String sql, boolean trimDelimiter)
    {
        // This is called only when use selects query (i.e. no automatic query detection)
        if (sql.isEmpty() || !trimDelimiter) {
            // Do not trim delimiter
            return sql;
        }

        // Here we cur trailing query delimiter. most of DBs don't expect it in the end of query
        // However Oracle REQUIRES that block queries (e.g. DDL like CREATE PROCEDURE) must have trailing delimiter
        // So we check whether this query is a block query (by checking for all SQL dialect block delimiters)
        String trailingSpaces = "";
        {
            int trailingSpacesCount = 0;
            for (int i = sql.length() - 1; i >= 0; i--) {
                if (!Character.isWhitespace(sql.charAt(i))) {
                    break;
                }
                trailingSpacesCount++;
            }
            if (trailingSpacesCount > 0) {
                trailingSpaces = sql.substring(sql.length() - trailingSpacesCount);
                sql = sql.substring(0, sql.length() - trailingSpacesCount);
            }
        }
        for (String statementDelimiter : syntaxManager.getStatementDelimiters()) {
            if (!sql.endsWith(statementDelimiter) && sql.length() > statementDelimiter.length()) {
                continue;
            }
            if (Character.isAlphabetic(statementDelimiter.charAt(0))) {
                // Delimiter is alphabetic (e.g. "GO") so it must be prefixed with whitespace
                char lastChar = sql.charAt(sql.length() - statementDelimiter.length() - 1);
                if (Character.isUnicodeIdentifierPart(lastChar)) {
                    break;
                }
            }
            // Remove trailing delimiter only if it is not block end
            boolean isBlockQuery = false;
            String trimmed = sql.substring(0, sql.length() - statementDelimiter.length());
            {
                String test = trimmed.toUpperCase().trim();
                String[][] blockBoundStrings = syntaxManager.getDialect().getBlockBoundStrings();
                if (blockBoundStrings != null) {
                    for (String[] blocks : blockBoundStrings) {
                        int endIndex = test.lastIndexOf(blocks[1]);
                        if (endIndex > 0) {
                            // This is a block query if it ends with 'END' or with 'END id'
                            if (test.endsWith(blocks[1])) {
                                isBlockQuery = true;
                                break;
                            } else {
                                String afterEnd = test.substring(endIndex + blocks[1].length()).trim();
                                if (afterEnd.chars().noneMatch(Character::isWhitespace)) {
                                    isBlockQuery = true;
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            if (!isBlockQuery) {
                sql = trimmed;
            }
            break;
        }
        return sql + trailingSpaces;
    }

    public static boolean isBlockStartKeyword(SQLDialect dialect, String keyword) {
        String[][] blockBoundStrings = dialect.getBlockBoundStrings();
        if (blockBoundStrings != null) {
            for (String[] block : blockBoundStrings) {
                if (block.length > 0 && keyword.equalsIgnoreCase(block[0])) {
                    return true;
                }
            }
        }
        return false;
    }

    public static boolean isBlockEndKeyword(SQLDialect dialect, String keyword) {
        String[][] blockBoundStrings = dialect.getBlockBoundStrings();
        if (blockBoundStrings != null) {
            for (String[] block : blockBoundStrings) {
                if (block.length > 1 && keyword.equalsIgnoreCase(block[1])) {
                    return true;
                }
            }
        }
        return false;
    }

    @NotNull
    public static SQLDialect getDialectFromObject(DBPObject object)
    {
        if (object instanceof DBSObject) {
            DBPDataSource dataSource = ((DBSObject)object).getDataSource();
            return getDialectFromDataSource(dataSource);
        }
        return BasicSQLDialect.INSTANCE;
    }

    @NotNull
    public static SQLDialect getDialectFromDataSource(@Nullable DBPDataSource dataSource) {
        return dataSource == null ? BasicSQLDialect.INSTANCE : dataSource.getSQLDialect();
    }

    public static void appendConditionString(
        @NotNull DBDDataFilter filter,
        @NotNull DBPDataSource dataSource,
        @Nullable String conditionTable,
        @NotNull StringBuilder query,
        boolean inlineCriteria)
    {
        appendConditionString(filter, dataSource, conditionTable, query, inlineCriteria, false);
    }

    public static void appendConditionString(
        @NotNull DBDDataFilter filter,
        @NotNull DBPDataSource dataSource,
        @Nullable String conditionTable,
        @NotNull StringBuilder query,
        boolean inlineCriteria,
        boolean subQuery
    ) {
        dataSource.getSQLDialect().getQueryGenerator().appendConditionString(filter, dataSource, conditionTable, query, inlineCriteria,
            subQuery);
    }

    public static void appendConditionString(
        @NotNull DBDDataFilter filter,
        @NotNull List<DBDAttributeConstraint> constraints,
        @NotNull DBPDataSource dataSource,
        @Nullable String conditionTable,
        @NotNull StringBuilder query,
        boolean inlineCriteria,
        boolean subQuery) {
        dataSource.getSQLDialect().getQueryGenerator().appendConditionString(filter, constraints, dataSource,
            conditionTable, query, inlineCriteria, subQuery);
    }

    public static void appendOrderString(
        @NotNull DBDDataFilter filter,
        @NotNull DBPDataSource dataSource,
        @Nullable String conditionTable,
        boolean subQuery,
        @NotNull StringBuilder query) {
        dataSource.getSQLDialect()
            .getQueryGenerator()
            .appendOrderString(filter, dataSource, conditionTable, subQuery, query);
    }

    @Nullable
    public static String getConstraintCondition(@NotNull DBPDataSource dataSource, @NotNull DBDAttributeConstraint constraint, @Nullable String conditionTable, boolean inlineCriteria) {
        return dataSource.getSQLDialect().getQueryGenerator().getConstraintCondition(dataSource, constraint,
            conditionTable,
            inlineCriteria);
    }

    private static String getStringValue(@NotNull DBPDataSource dataSource, @NotNull DBDAttributeConstraint constraint, boolean inlineCriteria, Object value) {
        String strValue;
        if (constraint.getAttribute() == null) {
            // We have only attribute name
            if (value instanceof CharSequence) {
                strValue = dataSource.getSQLDialect().getQuotedString(value.toString());
            } else {
                strValue = CommonUtils.toString(value);
            }
        } else if (inlineCriteria) {
            strValue = convertValueToSQL(dataSource, constraint.getAttribute(), value);
        } else {
            strValue = dataSource.getSQLDialect().getTypeCastClause(constraint.getAttribute(), "?", true);
        }
        return strValue;
    }

    public static int getConstraintOrderIndex(@NotNull DBDDataFilter dataFilter, @NotNull DBDAttributeConstraint constraint) {
        int index = dataFilter.getConstraints().indexOf(constraint);
        return index == -1 ? index : index + 1;
    }

    public static String convertValueToSQL(@NotNull DBPDataSource dataSource, @NotNull DBSTypedObject attribute, @Nullable Object value) {
        DBDValueHandler valueHandler = DBUtils.findValueHandler(dataSource, attribute);

        return convertValueToSQL(dataSource, attribute, valueHandler, value, DBDDisplayFormat.NATIVE);
    }

    public static String convertValueToSQL(@NotNull DBPDataSource dataSource, @NotNull DBSTypedObject attribute, @NotNull DBDValueHandler valueHandler, @Nullable Object value, DBDDisplayFormat displayFormat) {
        if (DBUtils.isNullValue(value)) {
            return SQLConstants.NULL_VALUE;
        }

        return dataSource.getSQLDialect().getTypeCastClause(
            attribute,
            convertValueToSQLFormat(dataSource, attribute, valueHandler, value, displayFormat), false);
    }

    private static String convertValueToSQLFormat(@NotNull DBPDataSource dataSource, @NotNull DBSTypedObject attribute, @NotNull DBDValueHandler valueHandler, @Nullable Object value, DBDDisplayFormat displayFormat) {
        if (DBUtils.isNullValue(value)) {
            return SQLConstants.NULL_VALUE;
        }

        String strValue;

        if (value instanceof DBDContent) {
            strValue = convertStreamToSQL(attribute, (DBDContent) value, valueHandler, dataSource);
        } else {
            strValue = valueHandler.getValueDisplayString(attribute, value, displayFormat);
        }
        SQLDialect sqlDialect = dataSource.getSQLDialect();

        DBPDataKind dataKind = attribute.getDataKind();
        switch (dataKind) {
            case CONTENT:
                if (value instanceof DBDContent) {
                    String contentType = ((DBDContent) value).getContentType();
                    if (contentType != null && !contentType.startsWith("text")) {
                        return strValue;
                    }
                }
                // Text content. Fall down
            case STRING:
            case ROWID:
                if (sqlDialect != null) {
                    return sqlDialect.getQuotedString(strValue);
                }
                return strValue;
            case BOOLEAN:
            case NUMERIC:
            default:
                if (sqlDialect != null) {
                    return sqlDialect.escapeScriptValue(attribute, value, strValue);
                }
                return strValue;
        }
    }

    public static String convertStreamToSQL(DBSTypedObject attribute, DBDContent content, DBDValueHandler valueHandler, DBPDataSource dataSource) {
        try {
            DBRProgressMonitor monitor = new VoidProgressMonitor();
            if (!content.isNull() && ContentUtils.isTextContent(content)) {
                return ContentUtils.getContentStringValue(monitor, content);
            } else {
                byte[] binValue = ContentUtils.getContentBinaryValue(monitor, content);
                if (binValue == null) {
                    return SQLConstants.NULL_VALUE;
                }
                return dataSource.getSQLDialect().getNativeBinaryFormatter().toString(binValue, 0, binValue.length);
            }
        }
        catch (Throwable e) {
            log.warn(e);
            return SQLConstants.NULL_VALUE;
        }
    }

    public static String getColumnTypeModifiers(@Nullable DBPDataSource dataSource, DBSTypedObject column, @NotNull String typeName, @NotNull DBPDataKind dataKind) {
        if (column == null) {
            return null;
        }
        if (dataSource == null) {
            return null;
        }
        return dataSource.getSQLDialect().getColumnTypeModifiers(dataSource, column, typeName, dataKind);
    }

    public static String getScriptDescripion(@NotNull String sql) {
        sql = stripComments(BasicSQLDialect.INSTANCE, sql);
        Matcher matcher = CREATE_PREFIX_PATTERN.matcher(sql);
        if (matcher.find() && matcher.start(0) == 0) {
            sql = sql.substring(matcher.end(1));
        }
        sql = sql.replaceAll(" +", " ");
        if (sql.length() > MAX_SQL_DESCRIPTION_LENGTH) {
            sql = sql.substring(0, MAX_SQL_DESCRIPTION_LENGTH) + " ...";
        }
        return sql;
    }

    @Nullable
    public static String getScriptDescription(@NotNull IFile sqlScript)
    {
        try {
            //log.debug("Read script '" + sqlScript.getName() + "' description");
            StringBuilder sql = new StringBuilder();
            try (BufferedReader is = new BufferedReader(new InputStreamReader(sqlScript.getContents()))) {
                for (;;) {
                    String line = is.readLine();
                    if (line == null) {
                        break;
                    }
                    line = line.trim();
                    if (line.startsWith(SQLConstants.SL_COMMENT) ||
                        line.startsWith("Rem") ||
                        line.startsWith("rem") ||
                        line.startsWith("REM")
                        )
                    {
                        continue;
                    }
                    sql.append(line).append('\n');
                    if (sql.length() > MIN_SQL_DESCRIPTION_LENGTH) {
                        break;
                    }
                }
            }
            return SQLUtils.getScriptDescripion(sql.toString());
        } catch (Exception e) {
            log.warn("", e);
        }
        return null;
    }

    public static String generateEntityAlias(DBSEntity entity, DBRFinder<Boolean, String> aliasFinder) {
        String name = entity.getName();
        if (CommonUtils.isEmpty(name)) {
            return name;
        }

        StringBuilder buf = new StringBuilder();
        boolean prevNonLetter = true;
        char prevChar = 0;
        for (int i = 0; i < name.length(); i++) {
            char c = name.charAt(i);
            if (!Character.isLetter(c)) {
                prevNonLetter = true;
            } else {
                if (prevNonLetter || (prevChar != 0 && Character.isLowerCase(prevChar) && Character.isUpperCase(c))) {
                    buf.append(c);
                }
                prevNonLetter = false;
            }
            prevChar = c;
        }
        String alias;
        if(!CommonUtils.isEmpty(buf)) {
            alias = buf.toString().toLowerCase(Locale.ENGLISH);
        }
        else{
            alias = "t";
        }

        String result = alias;
        for (int i = 2; i < 500; i++) {
            if (aliasFinder.findObject(result)) {
                result = alias + i;
            } else {
                return result;
            }
        }

        return alias;
    }

    @NotNull
    public static String generateCommentLine(@Nullable DBPDataSource dataSource, @NotNull String comment) {
        final String separator = GeneralUtils.getDefaultLineSeparator();
        String slComment = SQLConstants.SL_COMMENT;
        if (dataSource != null) {
            String[] slComments = dataSource.getSQLDialect().getSingleLineComments();
            if (!ArrayUtils.isEmpty(slComments)) {
                slComment = slComments[0];
            }
        }
        final StringBuilder sb = new StringBuilder();
        for (String line : comment.split("\n|\r|\r\n")) {
            sb.append(slComment).append(" ").append(line).append(separator);
        }
        return sb.toString();
    }

    public static String generateParamList(int paramCount) {
        if (paramCount == 0) {
            return "";
        } else if (paramCount == 1) {
            return "?";
        }
        StringBuilder sql = new StringBuilder("?");
        for (int i = 0; i < paramCount - 1; i++) {
            sql.append(",?");
        }
        return sql.toString();
    }

    /**
     * Replaces single \r linefeeds with space (some databases don't like them)
     */
    public static String fixLineFeeds(String sql) {
        if (sql.indexOf('\r') == -1) {
            return sql;
        }
        boolean hasFixes = false;
        char[] initial = sql.toCharArray();
        StringBuilder fixed = new StringBuilder(initial.length);
        for (int i = 0; i < initial.length; i++) {
            fixed.append(initial[i]);
            if (initial[i] == '\r') {
                if (i > 0 && initial[i - 1] == '\n') {
                    // \n\r
                    continue;
                }
                if (i == initial.length - 1 || initial[i + 1] == '\n') {
                    // \r\n
                    continue;
                }
                // Single \r - add \n after it to get \r\n sequence
                fixed.append('\n');
                hasFixes = true;
            }
        }
        return hasFixes ? fixed.toString() : sql;
    }

    /**
     * Compares two string ignoring extra whitespaces.
     * We can remove double whitespaces and any whitespaces between special chars (*-+,: etc).
     */
    public static boolean compareAliases(String str1, String str2) {
        if (str1 == null && str2 == null) {
            return true;
        }
        if (str1 == null || str2 == null) {
            return false;
        }
        return removeExtraSpaces(str1).equals(removeExtraSpaces(str2));
    }

    public static String removeExtraSpaces(@NotNull String str) {
        if (str.indexOf(' ') == -1) {
            return str;
        }
        StringBuilder result = new StringBuilder(str.length());
        int length = str.length();
        for (int i = 0; i < length; i++) {
            char c = str.charAt(i);
            if (Character.isWhitespace(c)) {
                boolean needsSpace = i > 0 && Character.isLetterOrDigit(str.charAt(i - 1));
                for (i = i + 1; i < length; i++) {
                    c = str.charAt(i);
                    if (Character.isWhitespace(c)) {
                        continue;
                    }
                    if (needsSpace && Character.isLetterOrDigit(c)) {
                        // We need exactly one space before letter/digit
                        result.append(' ');
                    } else {
                        // We don't need spaces before control symbols
                    }
                    result.append(c);
                    break;
                }
            } else {
                result.append(c);
            }
        }
        return result.toString();
    }

    @NotNull
    public static String generateScript(DBPDataSource dataSource, DBEPersistAction[] persistActions, boolean addComments)
    {
        final SQLDialect sqlDialect = SQLUtils.getDialectFromDataSource(dataSource);
        final String lineSeparator = GeneralUtils.getDefaultLineSeparator();

        StringBuilder script = new StringBuilder(64);
        if (addComments) {
            script.append(DBEAVER_DDL_COMMENT).append(Platform.getProduct().getName()).append(lineSeparator)
                .append(DBEAVER_DDL_WARNING).append(lineSeparator);
        }
        if (persistActions != null) {
            for (DBEPersistAction action : persistActions) {
                String scriptLine = action.getScript();
                if (CommonUtils.isEmpty(scriptLine)) {
                    continue;
                }

                String redefiner = sqlDialect.getScriptDelimiterRedefiner();
                String delimiter = getScriptLineDelimiter(sqlDialect);
                if (action.isComplex() && redefiner != null && !redefiner.equals(delimiter)) {
                    script.append(lineSeparator).append(redefiner).append(" ").append(DBEAVER_SCRIPT_DELIMITER).append(lineSeparator);
                    delimiter = DBEAVER_SCRIPT_DELIMITER;
                    script.append(delimiter).append(lineSeparator);
                } else if (action.getType() == DBEPersistAction.ActionType.COMMENT) {
                    if (script.length() > 2) {
                        int lfCount = 0;
                        for (int i = script.length() - 1; i >= 0; i--) {
                            if (!Character.isWhitespace(script.charAt(i))) {
                                break;
                            }
                            if (script.charAt(i) == '\n') lfCount++;
                        }
                        if (lfCount < 2) {
                            // Add line feed if we do not have empty line before
                            script.append(lineSeparator);
                        }
                    }
                }
                script.append(scriptLine);
                if (action.getType() != DBEPersistAction.ActionType.COMMENT) {
                    String testLine = scriptLine.trim();
                    if (testLine.lastIndexOf(delimiter) != (testLine.length() - delimiter.length())) {
                        script.append(delimiter);
                    }
                } else {
                    script.append(lineSeparator);
                }
                script.append(lineSeparator);

                if (action.isComplex() && redefiner != null && !redefiner.equals(delimiter)) {
                    script.append(redefiner).append(" ").append(getScriptLineDelimiter(sqlDialect)).append(lineSeparator);
                }
            }
        }
        return script.toString();
    }

    @NotNull
    public static String generateComments(DBPDataSource dataSource, DBEPersistAction[] persistActions, boolean addComments)
    {
        final SQLDialect sqlDialect = SQLUtils.getDialectFromDataSource(dataSource);
        final String lineSeparator = GeneralUtils.getDefaultLineSeparator();

        StringBuilder script = new StringBuilder(64);
        if (addComments) {
            script.append(DBEAVER_DDL_COMMENT).append(Platform.getProduct().getName()).append(lineSeparator)
                .append(DBEAVER_DDL_WARNING).append(lineSeparator);
        }
        if (persistActions != null) {
            String slComment;
            String[] slComments = sqlDialect.getSingleLineComments();
            if (ArrayUtils.isEmpty(slComments)) {
                slComment = "--";
            } else {
                slComment = slComments[0];
            }
            for (DBEPersistAction action : persistActions) {
                if (action.getType() != DBEPersistAction.ActionType.COMMENT) {
                    String scriptLine = action.getTitle();
                    if (CommonUtils.isEmpty(scriptLine)) {
                        continue;
                    }
                    script.append(slComment).append(" ").append(scriptLine);
                } else {
                    String scriptLine = action.getScript();
                    if (CommonUtils.isEmpty(scriptLine)) {
                        continue;
                    }
                    script.append(scriptLine);
                }
                script.append(lineSeparator);
            }
        }
        return script.toString();
    }

    public static String getScriptLineDelimiter(SQLDialect sqlDialect) {
        String delimiter = SQLUtils.getDefaultScriptDelimiter(sqlDialect);
        if (!delimiter.isEmpty() && Character.isLetterOrDigit(delimiter.charAt(0))) {
            delimiter = ' ' + delimiter;
        }
        return delimiter;
    }

    public static String[] splitFullIdentifier(final String fullName, char nameSeparator, String[][] quoteStrings) {
        return splitFullIdentifier(fullName, String.valueOf(nameSeparator), quoteStrings, false);
    }

    public static String[] splitFullIdentifier(final String fullName, String nameSeparator, String[][] quoteStrings, boolean keepQuotes) {
        String name = fullName.trim();
        if (ArrayUtils.isEmpty(quoteStrings)) {
            return name.split(Pattern.quote(nameSeparator));
        }
        if (!name.contains(nameSeparator)) {
            name = keepQuotes ? name : DBUtils.getUnQuotedIdentifier(name, quoteStrings);
            return new String[]{name};
        }
        List<String> nameList = new ArrayList<>();
        while (!name.isEmpty()) {
            boolean hadQuotedPart = false;
            for (String[] quotePair : quoteStrings) {
                String startQuote = quotePair[0];
                String endQuote = quotePair[1];
                if (!CommonUtils.isEmpty(startQuote) && !CommonUtils.isEmpty(endQuote) && name.startsWith(startQuote)) {
                    int endPos = name.indexOf(endQuote, startQuote.length());
                    if (endPos != -1) {
                        // Quoted part
                        String partName = keepQuotes ?
                            name.substring(0, endPos + endQuote.length()) :
                            name.substring(startQuote.length(), endPos);
                        while (partName.endsWith(nameSeparator)) {
                            partName = partName.substring(0, partName.length() - 1);
                        }
                        if (!partName.isEmpty()) {
                            nameList.add(partName);
                        }
                        name = name.substring(endPos + endQuote.length()).trim();
                        hadQuotedPart = true;
                        break;
                    }
                }
            }
            if (!hadQuotedPart) {
                int endPos = name.indexOf(nameSeparator);
                if (endPos != -1) {
                    nameList.add(name.substring(0, endPos));
                    name = name.substring(endPos);
                } else {
                    nameList.add(name);
                    break;
                }
            }
            if (!name.isEmpty() && name.startsWith(nameSeparator)) {
                name = name.substring(nameSeparator.length()).trim();
            }
        }
        return nameList.toArray(new String[0]);
    }

    public static String generateTableJoin(DBRProgressMonitor monitor, DBSEntity leftTable, String leftAlias, DBSEntity rightTable, String rightAlias) throws DBException {
        // Try to find FK in left table referencing to right table
        String sql = generateTableJoinByAssociation(monitor, leftTable, leftAlias, rightTable, rightAlias);
        if (sql != null) return sql;

        // Now try right to left
        sql = generateTableJoinByAssociation(monitor, rightTable, rightAlias, leftTable, leftAlias);
        if (sql != null) return sql;

        // Try to find columns in left table which match unique key in right table
        sql = generateTableJoinByColumns(monitor, leftTable, leftAlias, rightTable, rightAlias);
        if (sql != null) return sql;

        // In reverse order
        sql = generateTableJoinByColumns(monitor, rightTable, rightAlias, leftTable, leftAlias);
        if (sql != null) return sql;

        return null;
    }

    private static String generateTableJoinByColumns(DBRProgressMonitor monitor, DBSEntity leftTable, String leftAlias, DBSEntity rightTable, String rightAlias) throws DBException {
        List<DBSEntityAttribute> leftIdentifier = new ArrayList<>(DBUtils.getBestTableIdentifier(monitor, leftTable));
        if (!leftIdentifier.isEmpty()) {
            List<DBSEntityAttribute> rightAttributes = new ArrayList<>();
            for (DBSEntityAttribute attr : leftIdentifier) {
                DBSEntityAttribute rightAttr = rightTable.getAttribute(monitor, attr.getName());
                if (rightAttr == null) {
                    break;
                }
                rightAttributes.add(rightAttr);
            }
            if (leftIdentifier.size() != rightAttributes.size()) {
                return null;
            }
            StringBuilder joinSQL = new StringBuilder();
            for (int i = 0; i < leftIdentifier.size(); i++) {
                joinSQL
                    .append(leftAlias).append(".").append(DBUtils.getQuotedIdentifier(leftIdentifier.get(i))).append(" = ")
                    .append(rightAlias).append(".").append(DBUtils.getQuotedIdentifier(rightAttributes.get(i)));
            }
            return joinSQL.toString();
        }
        return null;
    }

    private static String generateTableJoinByAssociation(DBRProgressMonitor monitor, DBSEntity leftTable, String leftAlias, DBSEntity rightTable, String rightAlias) throws DBException {
        Collection<? extends DBSEntityAssociation> associations = leftTable.getAssociations(monitor);
        if (!CommonUtils.isEmpty(associations)) {
            for (DBSEntityAssociation fk : associations) {
                if (fk instanceof DBSTableForeignKey && fk.getAssociatedEntity() == rightTable) {
                    return generateTablesJoin(monitor, (DBSTableForeignKey)fk, leftAlias, rightAlias);
                }
            }
        }
        return null;
    }

    private static String generateTablesJoin(DBRProgressMonitor monitor, DBSTableForeignKey fk, String leftAlias, String rightAlias) throws DBException {
        boolean hasCriteria = false;
        StringBuilder joinSQL = new StringBuilder();
        for (DBSEntityAttributeRef ar : fk.getAttributeReferences(monitor)) {
            if (ar instanceof DBSTableForeignKeyColumn) {
                if (hasCriteria) {
                    joinSQL.append(" AND ");
                }
                DBSTableForeignKeyColumn fkc = (DBSTableForeignKeyColumn)ar;
                joinSQL
                    .append(leftAlias).append(".").append(DBUtils.getQuotedIdentifier(fkc)).append(" = ")
                    .append(rightAlias).append(".").append(DBUtils.getQuotedIdentifier(fkc.getReferencedColumn()));
                hasCriteria = true;
            }
        }
        return joinSQL.toString();
    }

    public static String getTableAlias(DBSEntity table) {
        return CommonUtils.escapeIdentifier(table.getName());
    }

    public static void appendQueryConditions(
        DBPDataSource dataSource,
        @NotNull StringBuilder query,
        @Nullable String tableAlias,
        @Nullable DBDDataFilter dataFilter
    ) {
        dataSource.getSQLDialect().getQueryGenerator().appendQueryConditions(dataSource, query, tableAlias, dataFilter);
    }

    public static void appendQueryOrder(
        DBPDataSource dataSource,
        @NotNull StringBuilder query,
        @Nullable String tableAlias,
        @Nullable DBDDataFilter dataFilter
    ) {
        dataSource.getSQLDialect().getQueryGenerator().appendQueryOrder(dataSource, query, tableAlias, dataFilter);
    }

    public static boolean isExecQuery(@NotNull SQLDialect dialect, String query) {
        // Check for EXEC query
        final String[] executeKeywords = dialect.getExecuteKeywords();
        if (executeKeywords != null && executeKeywords.length > 0) {
            final String queryStart = getFirstKeyword(dialect, query);
            return isExecKeyword(dialect, queryStart);
        }
        return false;
    }

    public static boolean isExecKeyword(SQLDialect dialect, String word) {
        return ArrayUtils.containsIgnoreCase(dialect.getExecuteKeywords(), word);
    }

    public static String stripColumnTypeModifiers(String type) {
        int startPos = type.indexOf("(");
        if (startPos != -1) {
            int endPos = type.lastIndexOf(")");
            if (endPos != -1) {
                return type.substring(0, startPos);
            }
        }
        return type;
    }

    public static void fillQueryParameters(SQLQuery sqlStatement, List<SQLQueryParameter> parameters) {
        // Set values for all parameters
        // Replace parameter tokens with parameter values
        String query = sqlStatement.getText();
        for (int i = parameters.size(); i > 0; i--) {
            SQLQueryParameter parameter = parameters.get(i - 1);
            String paramValue = parameter.getValue();
            if (paramValue == null || paramValue.isEmpty()) {
                paramValue = SQLConstants.NULL_VALUE;
            }
            query = query.substring(0, parameter.getTokenOffset()) + paramValue + query.substring(parameter.getTokenOffset() + parameter.getTokenLength());
        }
        sqlStatement.setText(query);
        //sqlStatement.setOriginalText(query);
    }

    public static boolean needQueryDelimiter(SQLDialect sqlDialect, String query) {
        String[] scriptDelimiters = sqlDialect.getScriptDelimiters();
        for (String delimiter : scriptDelimiters) {
            if (!delimiter.isEmpty()) {
                if (Character.isLetterOrDigit(delimiter.charAt(0))) {
                    if (query.toUpperCase().endsWith(delimiter.toUpperCase())) {
                        if (!Character.isLetterOrDigit(query.charAt(query.length() - delimiter.length() - 1))) {
                            return true;
                        }
                    }
                } else {
                    return !query.endsWith(delimiter);
                }
            }
        }
        return false;
    }

    public static String removeQueryDelimiter(SQLDialect sqlDialect, String query) {
        String[] scriptDelimiters = sqlDialect.getScriptDelimiters();
        for (String delimiter : scriptDelimiters) {
            if (!delimiter.isEmpty() && query.contains(delimiter)) {
                String queryWithoutDelimiter = query.substring(0, query.lastIndexOf(delimiter));
                if (Character.isLetterOrDigit(delimiter.charAt(0))) {
                    if (query.toUpperCase().endsWith(delimiter.toUpperCase())) {
                        if (!Character.isLetterOrDigit(query.charAt(query.length() - delimiter.length() - 1))) {
                            return queryWithoutDelimiter;
                        }
                    }
                } else if (query.endsWith(delimiter)) {
                    return queryWithoutDelimiter;
                }
            }
        }
        return query;
    }

    public static String getDefaultScriptDelimiter(SQLDialect sqlDialect) {
        String[] scriptDelimiters = sqlDialect.getScriptDelimiters();
        if (!ArrayUtils.isEmpty(scriptDelimiters)) {
            return scriptDelimiters[0];
        }
        return SQLConstants.DEFAULT_STATEMENT_DELIMITER;
    }
}